package com.util.excel;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.text.csv.CsvReadConfig;
import cn.hutool.core.text.csv.CsvReader;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.Util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.pojo.Radius;
import lombok.extern.slf4j.Slf4j;
import org.mozilla.universalchardet.UniversalDetector;

import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.function.Consumer;

/**
 * @Description: 读取xlsx或csv
 * @Author: hechaobo
 * @Date: 2023/10/11
 **/
@Slf4j
public class FileUtilH {
    public static void read(String filename, Listener listener) {
        if (filename.endsWith(".csv")) {
            EasyExcel.read(filename, listener).charset(Charset.forName(getCharsetName(filename))).sheet().doRead();
        } else {
            EasyExcel.read(filename, listener).sheet().doRead();
        }
    }


    public static void read(String filename, String encode, Listener listener) {
        if (filename.endsWith(".csv")) {
            EasyExcel.read(filename, listener).excelType(ExcelTypeEnum.CSV).charset(Charset.forName(encode)).sheet().doRead();
        } else {
            EasyExcel.read(filename, listener).sheet().doRead();
        }
    }

    public static void read(String filename, int no, Listener listener) {
        EasyExcel.read(filename, listener).sheet(no).doRead();
    }

    public static void read(InputStream inputStream, Listener listener) {

        EasyExcel.read(inputStream, listener).sheet().doRead();

    }

    public static <T> void read(String filename, Class<T> cls, Consumer<T> consumer) {
       read(filename,1,cls,consumer);
    }
    public static <T> void read(String filename, int row ,Class<T> cls, Consumer<T> consumer) {
        ExcelReaderBuilder read = EasyExcel.read(filename, cls, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                consumer.accept(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
            }

            @Override
            public void onException(Exception exception, AnalysisContext context) throws Exception {

            }
        });
        if (filename.endsWith("csv")) {
            read.excelType(ExcelTypeEnum.CSV).charset(Charset.forName(getCharsetName(filename))).sheet().headRowNumber(row).doRead();
        } else read.sheet().headRowNumber(row).doRead();
    }

    public static <T> void read(String filename, Class<T> cls, AnalysisEventListener<T> listener) {
        read(filename, 1, cls, listener);
    }

    public static <T> void read(String filename, int row, Class<T> cls, AnalysisEventListener<T> listener) {
        EasyExcel.read(filename, cls, listener).charset(Charset.forName(getCharsetName(filename))).headRowNumber(row).sheet().doRead();
    }

    public static <T> List<T> read(String filename, Class<T> cls) {
        List<T> list = new LinkedList<>();
        ExcelReaderBuilder read = EasyExcel.read(filename, cls, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                list.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {

            }

            @Override
            public void onException(Exception exception, AnalysisContext context) throws Exception {
                exception.printStackTrace();
            }
        });
        if (filename.endsWith("csv")) {
            read.excelType(ExcelTypeEnum.CSV).charset(Charset.forName(getCharsetName(filename))).sheet().doRead();
        } else {
            read.sheet().doRead();
        }
        return list;
    }


    public static void validate(String filename, Class cls, int row) {
        List<String> columnNames = new ArrayList<String>();
        Field[] declaredFields = cls.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                columnNames.add(annotation.value()[0]);
            }
        }
        try {
            ExcelReaderBuilder read = EasyExcel.read(filename, new AnalysisEventListener<Map<String, String>>() {

                @Override
                public void invoke(Map map, AnalysisContext analysisContext) {

                }


                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {

                }

                @Override
                public void invokeHeadMap(Map headMap, AnalysisContext context) {
                    super.invokeHeadMap(headMap, context);
                    StringBuilder error = new StringBuilder();
                    columnNames.forEach(c -> {
                        if (!headMap.values().contains(c)) {
                            error.append("字段").append(c).append("不存在;");
                        }
                    });
                    if (error.length() > 0)
                        throw new RuntimeException(error.toString());
                    throw new RuntimeException("ok");
                }

            });
            if (filename.endsWith(".csv")) {
                read.excelType(ExcelTypeEnum.CSV).headRowNumber(row).sheet().doRead();
            } else {
                read.sheet().headRowNumber(row).doRead();
            }
        } catch (Exception e) {
            if (!e.getMessage().equals("ok")) throw e;
        }
    }


    public static void validate(String filename, Class cls) {
        List<String> columnNames = new ArrayList<String>();
        Field[] declaredFields = cls.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            declaredField.setAccessible(true);
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                columnNames.add(annotation.value()[0]);
            }
        }
        log.info("需要的表头，{}", columnNames);
        try {
            ExcelReaderBuilder read = EasyExcel.read(filename, new AnalysisEventListener<Map<String, String>>() {

                @Override
                public void invoke(Map map, AnalysisContext analysisContext) {

                }


                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {

                }

                @Override
                public void invokeHeadMap(Map headMap, AnalysisContext context) {
                    super.invokeHeadMap(headMap, context);
                    StringBuilder error = new StringBuilder();
                    columnNames.forEach(c -> {
                        if (!headMap.values().contains(c)) {
                            error.append("字段").append(c).append("不存在;");
                        }
                    });
                    if (error.length() > 0)
                        throw new RuntimeException(error.toString());
                    throw new RuntimeException("ok");
                }

            });
            if (filename.endsWith(".csv")) {
                read.excelType(ExcelTypeEnum.CSV).sheet().doRead();
            } else {
                read.sheet().doRead();
            }
        } catch (Exception e) {
            if (!e.getMessage().equals("ok")) throw e;
        }
    }


    /**
     * 自定义分隔符读取csv
     *
     * @param path      文件
     * @param separator 分隔符
     * @param cls       实体类
     * @param consumer
     * @param <T>
     */
    public static <T> void readCsv(String path, char separator, Class<T> cls, Consumer<T> consumer) {
        CsvReadConfig csvConfig = new CsvReadConfig();
        csvConfig.setContainsHeader(true);
        csvConfig.setFieldSeparator(separator);
        Map<String, String> head = new HashMap<>();
        for (Field field : ReflectUtil.getFields(cls)) {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                head.put(annotation.value()[0], field.getName());
            }
        }
        csvConfig.setHeaderAlias(head);
        CsvReader reader = CsvUtil.getReader(FileUtil.getReader(path, Charset.forName("utf-8")), csvConfig);
        reader.read(handler -> {
            T t = BeanUtil.toBean(handler.getFieldMap(), cls);
            consumer.accept(t);
        });
    }

    public static <T> void readCsvH(String path, String sep, Class<T> cls, Consumer<T> consumer) {
        Map<String, String> head = new HashMap<>();
        for (Field field : ReflectUtil.getFields(cls)) {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                head.put(field.getName(), annotation.value()[0]);
            }
        }
        BufferedReader reader = FileUtil.getReader(path, Charset.forName("utf-8"));
        try {
            String s = reader.readLine();
            String[] split = s.split(sep);
            Map<String, Integer> map = new HashMap<>();
            for (int i = 0; i < split.length; i++) {
                map.put(split[i], i);
            }
            Map<String, Integer> filedMap = new HashMap<>();
            head.forEach((k, v) -> {
                filedMap.put(k, map.get(v));
            });
            Map<String, String> value = new HashMap<>();
            String line;
            while ((line = reader.readLine()) != null) {
                String[] split1 = line.split(sep);
                filedMap.forEach((k, v) -> {
                    value.put(k, split1[v]);
                });
                T bean = BeanUtil.toBean(value, cls);
                consumer.accept(bean);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                reader.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void readCsv(String path, String charset, Consumer<List<String>> consumer) {
        CsvReadConfig csvConfig = new CsvReadConfig();
        csvConfig.setContainsHeader(true);
        csvConfig.setFieldSeparator(',');
        CsvReader reader = CsvUtil.getReader(FileUtil.getReader(path, Charset.forName(charset)), csvConfig);
        reader.read(handler -> {
            List<String> rawList = handler.getRawList();
            consumer.accept(rawList);
        });
    }


    /**
     * 获取文件编码方式
     */
    public static String getCharsetName(String path) {
        String encoding = "utf-8";
        BufferedReader reader = FileUtil.getReader(path, encoding);
        try {
            String s = reader.readLine() + reader.readLine();
            for (char c : s.toCharArray()) {
                //乱码
                if ((int) c == 65533) {
                    return "gbk";
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return "utf-8";
    }

    /**
     * 根据某列统计次数
     *
     * @param path 文件
     * @param res  结果
     * @param key  根据这一列
     * @param col  附加展示列
     */
    public static void countNumberColumns(String path,
                                          String res,
                                          String key,
                                          String... col) {
        Map<String, List> map = new HashMap<>();
        read(path, new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                String name = getByColName(key);
                List list = map.get(name);
                if (list == null) {
                    list = new ArrayList();
                    list.add(0);
                    list.add(getByColName(key));
                    for (String s : col) {
                        list.add(getByColName(s));
                    }
                    map.put(name, list);
                }
                list.set(0, (int) list.get(0) + 1);
            }
        });
        List head = new LinkedList();
        head.add(ListUtil.toList("统计数"));
        head.add(ListUtil.toList(key));
        for (String s : col) {
            head.add(ListUtil.toList(s));
        }
        EasyExcel.write(res).sheet().head(head).doWrite(map.values());
    }

    /**
     * @param path1       底表
     * @param path2       增加
     * @param path3       结果
     * @param key1
     * @param key2
     * @param saveNoMatch 是否保留未匹配的行 true保留
     * @param col         增加的列
     */
    public static void merge(String path1, String path2, String path3, String key1, String key2, boolean saveNoMatch, String... col) {

        ExcelWriter writer = EasyExcel.write(path3).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();

        Map<String, List> map = new HashMap<>(50000);
        List header = new LinkedList();
        FileUtilH.read(path2, new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                List list = new LinkedList();
                for (String s : col) {
                    list.add(getByColName(s));
                }
                map.put(getByColName(key2), list);
            }

        });
        log.info("map ok");
        List data = new LinkedList();
        data.add(header);
        FileUtilH.read(path1, new Listener() {
            int i = 0;

            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                ArrayList<String> strings = ListUtil.toList(integerStringMap.values());
                List c = map.get(getByColName(key1));
                if (c != null) {
                    strings.addAll(c);
                } else {
                    //为空且不保留
                    if (!saveNoMatch) {
                        return;
                    }
                }
                data.add(strings);
                if (data.size() == 100000) {
                    log.info("{}", i++);
                    writer.write(data, writeSheet);
                    data.clear();
                }
            }

            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                super.invokeHeadMap(headMap, context);
                header.addAll(headMap.values());
                header.addAll(ListUtil.toList(col));
            }
        });
        writer.write(data, writeSheet);
        writer.close();
    }

    /**
     * 合并csv文件
     *
     * @param path1
     * @param path2
     */
    public static void appendCsv(String path1, String path2) {
        BufferedWriter writer = FileUtil.getWriter(path1, Charset.forName("UTF-8"), true);
        BufferedReader reader = FileUtil.getReader(path2, Charset.forName("UTF-8"));
        try {
            reader.readLine();//去除表头
            String str = reader.readLine();
            while (str != null) {
                writer.write(str);
                writer.newLine();
                str = reader.readLine();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        log.info("追加完成");
    }

    public static void merge(String path1, String path2, String key1, String key2, String... col) {

        List<List> data = new LinkedList<>();
        Map<String, List> map = new HashMap<>(50000);
        FileUtilH.read(path1, new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                map.put(getByColName(key1), new LinkedList(integerStringMap.values()));
            }

            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                super.invokeHeadMap(headMap, context);
                LinkedList<String> strings = new LinkedList<>(headMap.values());
                strings.addAll(ListUtil.toList(col));
                data.add(strings);
            }
        });
        FileUtilH.read(path2, new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                List c = new LinkedList(map.get(getByColName(key2)));
                for (String s : col) {
                    c.add(getByColName(s));
                }
                data.add(c);
            }
        });
        EasyExcel.write(path1).sheet().doWrite(data);
    }

    /**
     * 内连接
     * 少数存入map，遍历多数
     *
     * @param path1 多数
     * @param path2 少数
     * @param res
     * @param key1
     * @param key2
     * @param col
     */
    public static void innerJoin(String path1, String path2, String res, String key1, String key2, String... col) {
        WriterH writerH = new WriterH(res);

        Map<String, List<List>> map = new HashMap<>(10000);
        FileUtilH.read(path2, new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                List<List> lists = map.computeIfAbsent(getByColName(key2), k -> new LinkedList<>());
                ArrayList<String> strings = new ArrayList<>();
                for (String s : col) {
                    strings.add(getByColName(s));
                }
                lists.add(strings);
            }

        });
        FileUtilH.read(path1, new Listener() {
            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                super.invokeHeadMap(headMap, context);
                LinkedList<String> strings = new LinkedList<>(headMap.values());
                strings.addAll(ListUtil.toList(col));
                writerH.write(strings);
            }
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                super.invoke(integerStringMap, analysisContext);
                List<List> lists = map.get(getByColName(key1));
                if (lists != null) {
                    for (List list : lists) {
                        ArrayList<String> strings = new ArrayList<>(integerStringMap.values());
                        strings.addAll(list);
                        writerH.write(strings);
                    }
                }
            }
        });
        writerH.close();
    }

    public static List<List<String>> read(String s) {
        List<List<String>> res = new LinkedList<>();
        EasyExcel.read(s, new Listener() {
            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                res.add(ListUtil.toList(integerStringMap.values()));
            }
        }).sheet().headRowNumber(0).doRead();
        return res;
    }
}
